﻿unit sqlstrtodelphi;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters,
  cxContainer, cxEdit, dxSkinsCore, dxSkinsDefaultPainters, cxTextEdit, cxMemo,
  ExtCtrls, Menus, StdCtrls, cxButtons, cxSplitter, cxMaskEdit, cxButtonEdit,
  ExtDlgs,ComObj;

type
  Tfrmsqlstrtodelphi = class(TForm)
    Panel1: TPanel;
    cxSplitter1: TcxSplitter;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    Panel6: TPanel;
    cxMemo1: TcxMemo;
    cxButton1: TcxButton;
    cxButton2: TcxButton;
    cxMemo2: TcxMemo;
    Button1: TButton;
    Button2: TButton;
    btn1: TButton;
    cxButton3: TcxButton;
    OpenTextFileDialog1: TOpenTextFileDialog;
    choosefile: TcxButtonEdit;
    lbl1: TLabel;
    lbl2: TLabel;
    edtsheetcounts: TcxTextEdit;
    cbbcomboxtablenames: TComboBox;
    procedure cxButton1Click(Sender: TObject);
    procedure cxButton2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure btn1Click(Sender: TObject);
    procedure cxButton3Click(Sender: TObject);
    procedure choosefilePropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmsqlstrtodelphi: Tfrmsqlstrtodelphi;

implementation

{$R *.dfm}

{sqlתdelphi}
procedure Tfrmsqlstrtodelphi.btn1Click(Sender: TObject);
begin
  ;
end;

procedure Tfrmsqlstrtodelphi.Button1Click(Sender: TObject);
begin
   cxMemo1.Text := FloatToStr(StrToDateTime(cxMemo1.Text));
end;

procedure Tfrmsqlstrtodelphi.Button2Click(Sender: TObject);
begin
  cxMemo1.Text := FormatDateTime('yyyy-mm-dd h:mm:ss',StrToFloat(cxMemo1.Text));
end;

procedure Tfrmsqlstrtodelphi.choosefilePropertiesButtonClick(Sender: TObject;
  AButtonIndex: Integer);
var
  myexcel:OleVariant;
  I:integer;
begin
  if OpenTextFileDialog1.Execute()  then
  begin
     choosefile.Text := OpenTextFileDialog1.FileName;
  end;

  if not FileExists(choosefile.Text)  then
  //未发Excel路径
  begin
    ShowMessage('未发现表');
    exit;
  end;

  try
    myexcel := Createoleobject('Excel.Application');
  except
    ShowMessage('无法创建Excel对象，请确认是否正确安装Excel');
    Exit;
  end;

   try
     myexcel.Workbooks.Open(choosefile.Text);
     edtsheetcounts.Text := IntToStr(myexcel.worksheets.count);
     cbbcomboxtablenames.Items.Clear;
     for I := 1 to myexcel.worksheets.count  do
     begin
        cbbcomboxtablenames.Items.Add(WideString(myexcel.Sheets[i].Name));
     end;
     cbbcomboxtablenames.ItemIndex := 0;
     cbbcomboxtablenames.Text := cbbcomboxtablenames.Text;
   finally
      myexcel.Quit;
   end;
end;

procedure Tfrmsqlstrtodelphi.cxButton1Click(Sender: TObject);
var
 I:integer;
 sqltemp:string;
begin
  cxMemo2.Lines.Clear;
  for I := 0 to cxMemo1.Lines.Count - 1 do
  begin
    sqltemp := cxMemo1.Lines[I];
    sqltemp := StringReplace(sqltemp,'''','''''',[rfReplaceAll,rfIgnoreCase]);
    if I = 0  then
    begin
       sqltemp := ''' '+sqltemp+' ''';
    end
    else
    begin
       sqltemp := '+'' '+sqltemp+' ''';
    end;
    cxMemo2.Lines.Add(sqltemp);
  end;
end;

procedure Tfrmsqlstrtodelphi.cxButton2Click(Sender: TObject);
var
 I:integer;
 sqltemp:string;
begin
  cxMemo1.Lines.Clear;
  for I := 0 to cxMemo2.Lines.Count - 1 do
  begin
    sqltemp := cxMemo2.Lines[I];
    sqltemp := StringReplace(sqltemp,'''''','@',[rfReplaceAll,rfIgnoreCase]);
    sqltemp := StringReplace(sqltemp,'''','',[rfReplaceAll,rfIgnoreCase]);
    sqltemp := StringReplace(sqltemp,'@','''',[rfReplaceAll,rfIgnoreCase]);
    sqltemp := Trim(sqltemp);
    if Copy( sqltemp,1,1)='+' then
    begin
       sqltemp := Copy(sqltemp,2,Length(sqltemp)-1);
    end;
    cxMemo1.Lines.Add(sqltemp);
  end;
end;

procedure Tfrmsqlstrtodelphi.cxButton3Click(Sender: TObject);
var
  iRow,iRowCnt:integer;
  myexcel:OleVariant;
  lvalue,lDescription:string;
  ltablename,StoreKid:string;
  Materialname,spec,qrkc,jl,cl,yh,sh,kc,jc,hsrq:string;
  proj_id:string;
  sql:string;
begin
   if not FileExists(choosefile.Text)  then
    //未发Excel路径
    begin
      ShowMessage('未发现表');
      exit;
    end;

    try
      myexcel := Createoleobject('Excel.Application');
    except
      ShowMessage('无法创建Excel对象，请确认是否正确安装Excel');
      Exit;
    end;

    cxMemo1.Lines.Clear;
    try
      myexcel.Workbooks.Open(choosefile.Text);
      myexcel.WorkSheets[cbbcomboxtablenames.Text].activate;
      iRowCnt := 744110;
      for iRow:=2 to iRowCnt do
      begin
        if (Trim(myexcel.Cells[iRow,1].Value) = '')
           and (Trim(myexcel.Cells[iRow,2].Value) = '')
           and (Trim(myexcel.Cells[iRow,3].Value) = '')
           and (Trim(myexcel.Cells[iRow,4].Value) = '')
           and (Trim(myexcel.Cells[iRow,5].Value) = '') then break;  //说明行已经到尽头

        //qrykc,jl,cl,yh,sh,kc,jc,hsrq
        MaterialName := Trim((myexcel.Cells[iRow,1].Value));
        qrkc := Trim((myexcel.Cells[iRow,2].Value));
        jl :=  Trim((myexcel.Cells[iRow,3].Value));
        cl := Trim((myexcel.Cells[iRow,4].Value));
        yh := Trim((myexcel.Cells[iRow,5].Value));
        sh := Trim((myexcel.Cells[iRow,6].Value));
        kc := Trim((myexcel.Cells[iRow,7].Value));
        jc := Trim((myexcel.Cells[iRow,8].Value));
        hsrq := Trim((myexcel.Cells[iRow,9].Value));
        proj_id := '49521';
        StoreKid := 'hvzU0lNmQuiMpJ22P6RR8g';

        spec := '';
        if qrkc='' then   qrkc := '0';
        if jl='' then   jl := '0';
        if cl='' then   cl := '0';
        if yh='' then   yh := '0';
        if sh='' then   sh := '0';
        if kc='' then   kc := '0';
        if jc='' then   jc := '0';

        //material,spec 处理
        if Materialname = '0-3机制砂' then
        begin
          Materialname :='机制砂';
          spec := '0-2.36mm';
        end
        else if Materialname = '3-6碎石' then
        begin
           Materialname :='反击破碎石';
          spec := '2.36-4.75mm';
        end
        else if Materialname = '0-5碎石' then
        begin
           Materialname :='反击破碎石';
          spec := '4.75-9.5mm';
        end
        else if Materialname = '12碎石' then
        begin
           Materialname :='反击破碎石';
          spec := '9.5-19mm';
        end
        else if Materialname = '13碎石' then
        begin
           Materialname :='反击破碎石';
          spec := '19-26.5mm';
        end
        else if Materialname = '上面层3-6碎石' then
        begin
           Materialname :='上面层碎石';
          spec := '2.36-4.75mm';
        end
        else if Materialname = '上面层05碎石' then
        begin
           Materialname :='上面层碎石';
          spec := '4.75-9.5mm';
        end
        else if Materialname = '上面层12碎石' then
        begin
           Materialname :='上面层碎石';
          spec := '9.5-19mm';
        end
        else if Materialname = '普通水泥' then
        begin
          Materialname :='普通水泥';
          spec := 'P.042.5R';
        end
        else if Materialname = '矿粉' then
        begin
           Materialname :='矿粉';
          spec := '*';
        end
        else if Materialname = '基质沥青' then
        begin
           Materialname :='基质沥青';
          spec := '70#';
        end
        else if Materialname = '改性沥青' then
        begin
           Materialname :='改性沥青';
          spec := 'SBS-I-D';
        end;



        sql := 'update dbo.LM_Materialconsume set qrkc=%s ,slgb_in =%s,slgb_out = %s, slfll= %s, '+
        'sjxhl=%s,dqkc=%s where  material=''%s'' and spec = ''%s'' '+
        ' and curdate=''%s'' and proj_id =%s and StoreKid=''%s'' ';
        sql := Format(sql,[qrkc,jl,cl ,yh,sh,kc, MaterialName, spec,hsrq,proj_id,
        StoreKid]);

        cxMemo1.Lines.Add(sql);

      end;
    finally;
    end;

end;

end.
